/**
 * @swagger
 * /api/asset/financial-analysis:
 *   get:
 *     tags:
 *       - 资产管理
 *     summary: 获取资产财务分析数据
 *     description: 分析资产的财务指标、投资回报和成本效益，支持时间筛选
 *     parameters:
 *       - in: query
 *         name: startDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 开始日期 (YYYY-MM-DD)
 *         example: "2023-01-01"
 *       - in: query
 *         name: endDate
 *         schema:
 *           type: string
 *           format: date
 *         description: 结束日期 (YYYY-MM-DD)
 *         example: "2023-12-31"
 *     responses:
 *       200:
 *         description: 成功获取财务分析数据
 *         content:
 *           application/json:
 *             schema:
 *               type: object
 *               properties:
 *                 code:
 *                   type: integer
 *                   example: 200
 *                   description: 响应状态码
 *                 message:
 *                   type: string
 *                   example: "财务分析获取成功"
 *                   description: 响应消息
 *                 data:
 *                   type: object
 *                   properties:
 *                     fiscalAnalysis:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 财政拨款分析标题
 *                         overview:
 *                           type: object
 *                           properties:
 *                             totalAssets:
 *                               type: number
 *                               description: 总资产数
 *                             fiscalFundedAssets:
 *                               type: number
 *                               description: 财政拨款资产数
 *                             nonFiscalAssets:
 *                               type: number
 *                               description: 非财政拨款资产数
 *                             fiscalFundingRatio:
 *                               type: string
 *                               description: 财政拨款比例
 *                         funding:
 *                           type: object
 *                           properties:
 *                             totalFiscalFunding:
 *                               type: string
 *                               description: 总财政拨款
 *                             totalAssetValue:
 *                               type: string
 *                               description: 总资产价值
 *                             avgFiscalFunding:
 *                               type: string
 *                               description: 平均财政拨款
 *                             minFiscalFunding:
 *                               type: string
 *                               description: 最小财政拨款
 *                             maxFiscalFunding:
 *                               type: string
 *                               description: 最大财政拨款
 *                             fundingValueRatio:
 *                               type: string
 *                               description: 拨款价值比例
 *                     priceAnalysis:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 资产价格分析标题
 *                         overview:
 *                           type: object
 *                           properties:
 *                             assetsWithPrice:
 *                               type: number
 *                               description: 有价格的资产数
 *                             assetsWithoutPrice:
 *                               type: number
 *                               description: 无价格的资产数
 *                             priceDataRatio:
 *                               type: string
 *                               description: 价格数据比例
 *                         pricing:
 *                           type: object
 *                           properties:
 *                             totalValue:
 *                               type: string
 *                               description: 总价值
 *                             avgPrice:
 *                               type: string
 *                               description: 平均价格
 *                             minPrice:
 *                               type: string
 *                               description: 最低价格
 *                             maxPrice:
 *                               type: string
 *                               description: 最高价格
 *                             priceStdDev:
 *                               type: string
 *                               description: 价格标准差
 *                     priceDistribution:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 价格区间分布标题
 *                         ranges:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               priceRange:
 *                                 type: string
 *                                 description: 价格区间
 *                               assetCount:
 *                                 type: number
 *                                 description: 资产数量
 *                               totalValue:
 *                                 type: string
 *                                 description: 总价值
 *                               percentage:
 *                                 type: string
 *                                 description: 占比
 *                     categoryComparison:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 分类财务对比标题
 *                         categories:
 *                           type: array
 *                           items:
 *                             type: object
 *                             properties:
 *                               categoryName:
 *                                 type: string
 *                                 description: 类别名称
 *                               assetCount:
 *                                 type: number
 *                                 description: 资产数量
 *                               totalFiscalFunding:
 *                                 type: string
 *                                 description: 总财政拨款
 *                               totalAssetValue:
 *                                 type: string
 *                                 description: 总资产价值
 *                               avgFiscalFunding:
 *                                 type: string
 *                                 description: 平均财政拨款
 *                               avgAssetValue:
 *                                 type: string
 *                                 description: 平均资产价值
 *                               fundingRatio:
 *                                 type: string
 *                                 description: 拨款比例
 *                     highValueAssets:
 *                       type: object
 *                       properties:
 *                         title:
 *                           type: string
 *                           description: 高价值资产分析标题
 *                         statistics:
 *                           type: object
 *                           properties:
 *                             highValueAssets:
 *                               type: number
 *                               description: 高价值资产数（≥5万）
 *                             veryHighValueAssets:
 *                               type: number
 *                               description: 超高价值资产数（≥10万）
 *                             highValueTotal:
 *                               type: string
 *                               description: 高价值资产总值
 *                             veryHighValueTotal:
 *                               type: string
 *                               description: 超高价值资产总值
 *                             highValueRatio:
 *                               type: string
 *                               description: 高价值资产比例
 *                             veryHighValueRatio:
 *                               type: string
 *                               description: 超高价值资产比例
 *       500:
 *         description: 服务器内部错误
 */
import { withCors } from '../../../lib/cors';
import { success, error as errorResponse, handleResponse } from '../../../lib/response';
import { query } from '../../../lib/db';

/**
 * 处理资产财务分析请求
 * @param {Object} req - 请求对象
 * @param {Object} res - 响应对象
 */
async function handler(req, res) {
  try {
    const { startDate, endDate } = req.query;

    let whereClause = '';
    const params = [];

    if (startDate && endDate) {
      whereClause = 'WHERE PURCHASE_DATE BETWEEN ? AND ?';
      params.push(startDate, endDate);
    } else if (startDate) {
      whereClause = 'WHERE PURCHASE_DATE >= ?';
      params.push(startDate);
    } else if (endDate) {
      whereClause = 'WHERE PURCHASE_DATE <= ?';
      params.push(endDate);
    }
    
    // 财政拨款分析
    const fiscalAnalysisQuery = `
      SELECT 
        COUNT(*) as totalAssets,
        COUNT(CASE WHEN CAIZXJFLY > 0 THEN 1 END) as fiscalFundedAssets,
        COUNT(CASE WHEN CAIZXJFLY IS NULL OR CAIZXJFLY = 0 THEN 1 END) as nonFiscalAssets,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalAssetValue,
        AVG(COALESCE(CAIZXJFLY, 0)) as avgFiscalFunding,
        MIN(CASE WHEN CAIZXJFLY > 0 THEN CAIZXJFLY END) as minFiscalFunding,
        MAX(CAIZXJFLY) as maxFiscalFunding
      FROM wd_sec_assets12
      ${whereClause}
    `;

    const fiscalResult = await query(fiscalAnalysisQuery, params);
    const fiscalData = fiscalResult[0];

    // 价格分析
    const priceAnalysisQuery = `
      SELECT 
        COUNT(CASE WHEN STOCK_PRICE > 0 THEN 1 END) as assetsWithPrice,
        COUNT(CASE WHEN STOCK_PRICE IS NULL OR STOCK_PRICE = 0 THEN 1 END) as assetsWithoutPrice,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgPrice,
        MIN(CASE WHEN STOCK_PRICE > 0 THEN STOCK_PRICE END) as minPrice,
        MAX(STOCK_PRICE) as maxPrice,
        STDDEV(COALESCE(STOCK_PRICE, 0)) as priceStdDev
      FROM wd_sec_assets12
      ${whereClause}
    `;

    const priceResult = await query(priceAnalysisQuery, params);
    const priceData = priceResult[0];

    // 价格区间分布
    const priceRangeQuery = `
      SELECT 
        CASE 
          WHEN COALESCE(STOCK_PRICE, 0) = 0 THEN '无价格'
          WHEN STOCK_PRICE <= 1000 THEN '0-1千'
          WHEN STOCK_PRICE <= 5000 THEN '1千-5千'
          WHEN STOCK_PRICE <= 10000 THEN '5千-1万'
          WHEN STOCK_PRICE <= 50000 THEN '1万-5万'
          WHEN STOCK_PRICE <= 100000 THEN '5万-10万'
          ELSE '10万以上'
        END as priceRange,
        COUNT(*) as assetCount,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalValue
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY 
        CASE 
          WHEN COALESCE(STOCK_PRICE, 0) = 0 THEN '无价格'
          WHEN STOCK_PRICE <= 1000 THEN '0-1千'
          WHEN STOCK_PRICE <= 5000 THEN '1千-5千'
          WHEN STOCK_PRICE <= 10000 THEN '5千-1万'
          WHEN STOCK_PRICE <= 50000 THEN '1万-5万'
          WHEN STOCK_PRICE <= 100000 THEN '5万-10万'
          ELSE '10万以上'
        END
      ORDER BY 
        CASE 
          WHEN priceRange = '无价格' THEN 0
          WHEN priceRange = '0-1千' THEN 1
          WHEN priceRange = '1千-5千' THEN 2
          WHEN priceRange = '5千-1万' THEN 3
          WHEN priceRange = '1万-5万' THEN 4
          WHEN priceRange = '5万-10万' THEN 5
          ELSE 6
        END
    `;

    const priceRangeResult = await query(priceRangeQuery, params);

    // 财政拨款与资产价值对比分析
    const comparisonQuery = `
      SELECT 
        COALESCE(ASSETS_SORT_GGJ_NAME, '未分类') as categoryName,
        COUNT(*) as assetCount,
        SUM(COALESCE(CAIZXJFLY, 0)) as totalFiscalFunding,
        SUM(COALESCE(STOCK_PRICE, 0)) as totalAssetValue,
        AVG(COALESCE(CAIZXJFLY, 0)) as avgFiscalFunding,
        AVG(COALESCE(STOCK_PRICE, 0)) as avgAssetValue,
        SUM(COALESCE(CAIZXJFLY, 0)) / NULLIF(SUM(COALESCE(STOCK_PRICE, 0)), 0) * 100 as fundingRatio
      FROM wd_sec_assets12
      ${whereClause}
      GROUP BY ASSETS_SORT_GGJ_NAME
      HAVING totalAssetValue > 0
      ORDER BY totalAssetValue DESC
      LIMIT 10
    `;

    const comparisonResult = await query(comparisonQuery, params);

    // 高价值资产分析
    const highValueQuery = `
      SELECT 
        COUNT(CASE WHEN STOCK_PRICE >= 50000 THEN 1 END) as highValueAssets,
        COUNT(CASE WHEN STOCK_PRICE >= 100000 THEN 1 END) as veryHighValueAssets,
        SUM(CASE WHEN STOCK_PRICE >= 50000 THEN COALESCE(STOCK_PRICE, 0) END) as highValueTotal,
        SUM(CASE WHEN STOCK_PRICE >= 100000 THEN COALESCE(STOCK_PRICE, 0) END) as veryHighValueTotal
      FROM wd_sec_assets12
      ${whereClause}
    `;

    const highValueResult = await query(highValueQuery, params);
    const highValueData = highValueResult[0];

    const responseData = {
      fiscalAnalysis: {
        title: "财政拨款分析",
        overview: {
          totalAssets: parseInt(fiscalData.totalAssets),
          fiscalFundedAssets: parseInt(fiscalData.fiscalFundedAssets),
          nonFiscalAssets: parseInt(fiscalData.nonFiscalAssets),
          fiscalFundingRatio: ((parseInt(fiscalData.fiscalFundedAssets) / parseInt(fiscalData.totalAssets)) * 100).toFixed(2)
        },
        funding: {
          totalFiscalFunding: parseFloat(fiscalData.totalFiscalFunding || 0).toFixed(2),
          totalAssetValue: parseFloat(fiscalData.totalAssetValue || 0).toFixed(2),
          avgFiscalFunding: parseFloat(fiscalData.avgFiscalFunding || 0).toFixed(2),
          minFiscalFunding: parseFloat(fiscalData.minFiscalFunding || 0).toFixed(2),
          maxFiscalFunding: parseFloat(fiscalData.maxFiscalFunding || 0).toFixed(2),
          fundingValueRatio: parseFloat(fiscalData.totalAssetValue) > 0 ? 
            ((parseFloat(fiscalData.totalFiscalFunding) / parseFloat(fiscalData.totalAssetValue)) * 100).toFixed(2) : "0.00"
        }
      },
      priceAnalysis: {
        title: "资产价格分析",
        overview: {
          assetsWithPrice: parseInt(priceData.assetsWithPrice),
          assetsWithoutPrice: parseInt(priceData.assetsWithoutPrice),
          priceDataRatio: ((parseInt(priceData.assetsWithPrice) / parseInt(fiscalData.totalAssets)) * 100).toFixed(2)
        },
        pricing: {
          totalValue: parseFloat(priceData.totalValue || 0).toFixed(2),
          avgPrice: parseFloat(priceData.avgPrice || 0).toFixed(2),
          minPrice: parseFloat(priceData.minPrice || 0).toFixed(2),
          maxPrice: parseFloat(priceData.maxPrice || 0).toFixed(2),
          priceStdDev: parseFloat(priceData.priceStdDev || 0).toFixed(2)
        }
      },
      priceDistribution: {
        title: "价格区间分布",
        ranges: priceRangeResult.map(row => ({
          priceRange: row.priceRange,
          assetCount: parseInt(row.assetCount),
          totalValue: parseFloat(row.totalValue || 0).toFixed(2),
          percentage: ((parseInt(row.assetCount) / parseInt(fiscalData.totalAssets)) * 100).toFixed(2)
        }))
      },
      categoryComparison: {
        title: "分类财务对比(Top10)",
        categories: comparisonResult.map(row => ({
          categoryName: row.categoryName,
          assetCount: parseInt(row.assetCount),
          totalFiscalFunding: parseFloat(row.totalFiscalFunding || 0).toFixed(2),
          totalAssetValue: parseFloat(row.totalAssetValue || 0).toFixed(2),
          avgFiscalFunding: parseFloat(row.avgFiscalFunding || 0).toFixed(2),
          avgAssetValue: parseFloat(row.avgAssetValue || 0).toFixed(2),
          fundingRatio: parseFloat(row.fundingRatio || 0).toFixed(2)
        }))
      },
      highValueAssets: {
        title: "高价值资产分析",
        statistics: {
          highValueAssets: parseInt(highValueData.highValueAssets),
          veryHighValueAssets: parseInt(highValueData.veryHighValueAssets),
          highValueTotal: parseFloat(highValueData.highValueTotal || 0).toFixed(2),
          veryHighValueTotal: parseFloat(highValueData.veryHighValueTotal || 0).toFixed(2),
          highValueRatio: ((parseInt(highValueData.highValueAssets) / parseInt(fiscalData.totalAssets)) * 100).toFixed(2),
          veryHighValueRatio: ((parseInt(highValueData.veryHighValueAssets) / parseInt(fiscalData.totalAssets)) * 100).toFixed(2)
        }
      }
    };

    return handleResponse(res, success(responseData, "资产财务分析获取成功"));
    
  } catch (error) {
    console.error('Asset financial analysis error:', error);
    return handleResponse(res, errorResponse("获取资产财务分析失败", 500));
  }
}

export default withCors(handler, ['GET']);